package com.VolunteerPlatform.Service.UtilityService;

import com.VolunteerPlatform.Entity.TeamInfo;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by DoveXL on 2018/12/10.
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class ReadExcelService {

    private int totalRows = 0;
    //总条数
    private int totalCells = 0;
    //错误信息接收器
    private String errorMsg;

    public int getTotalCells() {
        return totalCells;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public String getErrorMsg() {
        return errorMsg;
    }

    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    public boolean validateExcel(String filePath){
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    public  List<TeamInfo> getExcelInfo(String filename) throws IOException {

        List<TeamInfo> teamInfoList = new ArrayList<TeamInfo>();
        InputStream is = null;
        //验证文件名是否合格
        if (!validateExcel(filename)) {
            return null;
        }
        boolean isExcel2003 = true;
        if (isExcel2007(filename)) {
            isExcel2003 = false;
        }
        //根据新建的文件实例化输入流
        File file = new File("D:\\code\\IntelIJ IDEA\\GeoLife\\LongHuaVolunteer\\target\\com.ecust\\files\\upload\\" + filename);
        is = new FileInputStream(file);
        Workbook wb = null;
        //当excel是2003时
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {//当excel是2007时
            wb = new XSSFWorkbook(is);
        }
        //读取Excel里面客户的信息
        teamInfoList = readTeamInfoExcelValue(wb);
        return teamInfoList;
    }

    private List<TeamInfo> readTeamInfoExcelValue(Workbook wb){
        Sheet sheet=wb.getSheetAt(0);
        this.totalRows=sheet.getPhysicalNumberOfRows();
        if(totalRows>=1 && sheet.getRow(0) != null){
            this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<TeamInfo> TeamInfoList=new ArrayList<>();
        for(int r=1;r<totalRows;r++){
            Row row = sheet.getRow(r);
            if (row == null) continue;
            if(row.getCell(0)!=null) {
                TeamInfo teamInfo = new TeamInfo();
                for (int c = 0; c < this.totalCells; c++) {
                    Cell cell = row.getCell(c);

                    if (c == 0) {
                            teamInfo.setTeamName(getCellValue(cell));
                    } else if (c == 1) {
                        if (cell == null) {
                            teamInfo.setTeamLeading("无");
                        } else {

                            teamInfo.setTeamLeading(getCellValue(cell));
                        }
                    } else if (c == 2) {
                        if (cell == null) {
                            teamInfo.setLeadingPhonenum("无");
                        } else {
                            teamInfo.setLeadingPhonenum(getCellValue(cell));
                        }
                    }
                    teamInfo.setTeamNumber(0);
                }
                if(teamInfo!=null) {
                    TeamInfoList.add(teamInfo);
                }
            }

        }
        return TeamInfoList;
    }


    private String getCellValue(Cell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue()).toString();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "无";
        }
        return cellValue;
    }
}
